import type { Router } from "express"
import type { Ioracle, IcallbackFn } from "../../types"
import { decrypt } from "../../utils/privateKey"
import { BindParameters } from "oracledb"
//import { BindParameters } from "oracledb";

interface result {
  measure_id: number
  measure_result: number
}

const MeasureOperate = (
  router: Router,
  oracle: Ioracle,
  callbackFn: IcallbackFn
) => {
  router.post("/disposal", (req, res) => {
    const { param, type } = req.body
    const paramParse = JSON.parse(param)
    let sql = ""
    if (type === "count") {
      sql = ` select count(*)count from xxcc_behavior_measure_header where klm = '${decrypt(
        paramParse.username
      )}' and status = 'N'`
      callbackFn(res, oracle.connHrm, { sql })
    } else {
      sql = ` select * from (select row_number() over(order by c.docid) as rowseq,c.* from(
            select docid,judge_period,badge,b.chinese_name,status from xxcc_behavior_measure_header,user_info_n b
            where klm = '${decrypt(
              paramParse.username
            )}' and status = 'N' and badge = b.work_num
            )c)
            where rowseq between ${
              (paramParse.current - 1) * paramParse.pagesize + 1
            } and ${paramParse.current * paramParse.pagesize}`
      callbackFn(res, oracle.connHrm, { sql })
    }
  })

  router.post("/getdetail", (req, res) => {
    const { docid } = req.body
    let sql = `select a.measure_id,b.measurement_header,b.measurement_detail from xxcc_behavior_measure_line a,xxcc_behavior_mesure_list b`
    sql += ` where a.measure_id = b.measure_id and a.docid = '${docid}'`
    callbackFn(res, oracle.connHrm, { sql })
  })

  router.post("/measuresetline", async (req, res) => {
    const { param, comments, docid } = req.body
    const paramParse = JSON.parse(param)
    let sql = ""
    try {
      sql = ` update xxcc_behavior_measure_line set measure_result = :result,last_update_time = sysdate
         where docid = :docid and measure_id = :measure_id`
      let bind: Array<BindParameters> = []
      paramParse.forEach((element: result) => {
        bind.push({
          docid: docid,
          measure_id: element.measure_id,
          result: element.measure_result,
        })
      })
      const result = await oracle.connHrm({
        sql: sql,
        isMulti: true,
        params: bind,
      })
      if (result.code === 200) {
        sql = ` update xxcc_behavior_measure_header set status = 'Y',comments = '${comments}' where docid = '${docid}'`
        callbackFn(res, oracle.connHrm, { sql })
      }
    } catch {
      res.send({ code: 0, message: "後台異常" })
    }
  })

  router.post("/getdeptbyorg", (req, res) => {
    const { org_code } = req.body
    let sql = ` select code from department where org_code = '${org_code}' order by code`
    callbackFn(res, oracle.connHrm, { sql })
  })

  router.post("/getquery", (req, res) => {
    const { param, pager, type } = req.body
    const paramParse = JSON.parse(param)
    const pagerParse = JSON.parse(pager)
    let sql = ` select ${
      type === "count" ? "count(*)count" : "*"
    } from (select row_number() over(order by c.docid) as rowseq,c.* from(`
    sql += ` select  a.docid,a.judge_period,a.badge,decode( a.status,'Y','已結案','N','未處置') status,a.comments,b.dept,b.chinese_name name 
    from  xxcc_behavior_measure_header a,user_info_n b where a.badge = b.work_num and b.job_status = 'employed'`
    if (paramParse.org_code) {
      sql += ` and b.organization_code = '${paramParse.org_code}'`
    }
    if (paramParse.dept) {
      sql += ` and b.dept ='${paramParse.dept}'`
    }
    if (paramParse.badge) {
      sql += ` and a.badge = '${paramParse.badge}'`
    }
    if (paramParse.year && paramParse.month) {
      sql += ` and a.judge_period = '${
        paramParse.year.toString() + paramParse.month
      }'`
    }
    sql += " )c)"
    if (type !== "count")
      sql += `
            where rowseq between ${
              (pagerParse.current - 1) * pagerParse.pagesize + 1
            } and ${pagerParse.current * pagerParse.pagesize}`
    callbackFn(res, oracle.connHrm, { sql })
  })

  router.post("/getquerydetail", (req, res) => {
    const { docid } = req.body
    let sql = ` select a.measure_result,b.measurement_header,b.measurement_detail`
    sql += ` from xxcc_behavior_measure_line a,xxcc_behavior_mesure_list b`
    sql += ` where a.docid = '${docid}' and a.measure_id= b.measure_id`
    callbackFn(res, oracle.connHrm, { sql })
  })

  return router
}

export default MeasureOperate
